USE [master]
GO

/****** Object:  Database [{0}]    Script Date: 11/19/2009 14:19:06 ******/
CREATE DATABASE [{0}] ON  PRIMARY 
( NAME = N'{0}', FILENAME = N'{1}' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'{0}_log', FILENAME = N'{2}' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB )
GO

ALTER DATABASE [{0}] SET COMPATIBILITY_LEVEL = 90
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [{0}].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO

ALTER DATABASE [{0}] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [{0}] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [{0}] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [{0}] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [{0}] SET ARITHABORT OFF 
GO

ALTER DATABASE [{0}] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [{0}] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [{0}] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [{0}] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [{0}] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [{0}] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [{0}] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [{0}] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [{0}] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [{0}] SET  DISABLE_BROKER 
GO

ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [{0}] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [{0}] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [{0}] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [{0}] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [{0}] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [{0}] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [{0}] SET  READ_WRITE 
GO

ALTER DATABASE [{0}] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [{0}] SET  MULTI_USER 
GO

ALTER DATABASE [{0}] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [{0}] SET DB_CHAINING OFF 
GO

USE [{0}]
GO

/****** Object:  Table [dbo].[Organism]    Script Date: 11/19/2009 14:20:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Organism](
	[AccessionVersion] [varchar](20) NOT NULL,
	[Organism] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Organism] PRIMARY KEY CLUSTERED 
(
	[AccessionVersion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[Gene]    Script Date: 11/19/2009 14:21:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Gene](
	[Id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
	[AccessionVersion] [varchar](20) NOT NULL,
	[GeneId] [varchar](20) NOT NULL,
	[GenbankId] [varchar](20) NOT NULL,
	[LocusTag] [varchar](20) NOT NULL,
	[Symbol] [varchar](max) NOT NULL,
	[Cog] [varchar](max) NOT NULL,
	[Translation] [varchar](max) NOT NULL,
	[Product] [varchar](max) NOT NULL,
	[ProteinId] [varchar](20) NOT NULL,
	[Note] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Gene] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Gene]  WITH NOCHECK ADD  CONSTRAINT [FK_Gene_Gene] FOREIGN KEY([Id])
REFERENCES [dbo].[Gene] ([Id])
GO

ALTER TABLE [dbo].[Gene] CHECK CONSTRAINT [FK_Gene_Gene]
GO

ALTER TABLE [dbo].[Gene]  WITH NOCHECK ADD  CONSTRAINT [FK_Gene_Organism] FOREIGN KEY([AccessionVersion])
REFERENCES [dbo].[Organism] ([AccessionVersion])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Gene] CHECK CONSTRAINT [FK_Gene_Organism]
GO

/****** Object:  Index [IX_Gene]    Script Date: 11/23/2009 12:43:11 ******/
CREATE NONCLUSTERED INDEX [IX_Gene] ON [dbo].[Gene] 
(
	[AccessionVersion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_Gene_1]    Script Date: 11/23/2009 12:44:36 ******/
CREATE NONCLUSTERED INDEX [IX_Gene_1] ON [dbo].[Gene] 
(
	[GeneId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_Gene_2]    Script Date: 11/23/2009 12:45:05 ******/
CREATE NONCLUSTERED INDEX [IX_Gene_2] ON [dbo].[Gene] 
(
	[AccessionVersion] ASC,
	[GeneId] ASC,
	[GenbankId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Hits]    Script Date: 11/19/2009 14:21:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Hits](
	[QueryId] [decimal](18, 0) NOT NULL,
	[HitId] [decimal](18, 0) NOT NULL,
	[Rank] [int] NOT NULL,
	[AlignmentLength] [int] NOT NULL,
	[BitScore] [float] NOT NULL,
	[EValue] [float] NOT NULL,
	[HitFrame] [int] NOT NULL,
	[HitFrom] [int] NOT NULL,
	[HitGaps] [int] NOT NULL,
	[HitLength] [int] NOT NULL,
	[HitSequence] [varchar](max) NOT NULL,
	[HitTo] [int] NOT NULL,
	[Identity] [int] NOT NULL,
	[Midline] [varchar](max) NOT NULL,
	[Positive] [int] NOT NULL,
	[QueryFrame] [int] NOT NULL,
	[QueryFrom] [int] NOT NULL,
	[QuerySequence] [varchar](max) NOT NULL,
	[QueryTo] [int] NOT NULL,
	[Score] [int] NOT NULL,
	[QueryLength] [int] NULL,
 CONSTRAINT [PK_Hits] PRIMARY KEY CLUSTERED 
(
	[QueryId] ASC,
	[HitId] ASC,
	[Rank] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique Id of query sequence (refers Gene table)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hits', @level2type=N'COLUMN',@level2name=N'QueryId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'UniqueId of hit sequence (refers genes table)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hits', @level2type=N'COLUMN',@level2name=N'HitId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Position of hit in set of hits for query. 1 is first, then 2, then etc.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hits', @level2type=N'COLUMN',@level2name=N'Rank'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Length of aligned subsequence found by BLAST' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hits', @level2type=N'COLUMN',@level2name=N'AlignmentLength'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Bit score.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hits', @level2type=N'COLUMN',@level2name=N'BitScore'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'BLAST E-Value' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hits', @level2type=N'COLUMN',@level2name=N'EValue'
GO

ALTER TABLE [dbo].[Hits] ADD  CONSTRAINT [DF_Hits_Rank_]  DEFAULT ((1)) FOR [Rank]
GO

/****** Object:  Index [ix_Hits_HitId]    Script Date: 11/23/2009 12:47:37 ******/
CREATE NONCLUSTERED INDEX [ix_Hits_HitId] ON [dbo].[Hits] 
(
	[HitId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
GO

/****** Object:  Index [ix_Hits_QueryId]    Script Date: 11/23/2009 12:47:55 ******/
CREATE NONCLUSTERED INDEX [ix_Hits_QueryId] ON [dbo].[Hits] 
(
	[QueryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
GO


